import os
import time

import xlsxwriter
import datetime
import mimetypes
from flask import Blueprint, request, jsonify, make_response
from flask_restful import Api, Resource
from flask import current_app as app
from uitils.comm import get_data
from uitils.comm import xtree
from uitils.myfile import myfile
from db import db
from io import BytesIO
from xlrd import open_workbook
from urllib.parse import quote
from uitils.es import Myes

firstbp = Blueprint('firstbp', __name__)
api = Api(firstbp)


class First(Resource):
    def get(self):
        str = '请求ip:' + request.remote_addr + ',请求的url' + request.url + ',错误信息' + '\n'
        app.logger.error(str)
        res = '牛逼'
        return {'cd': 'lll'}


api.add_resource(First, '/text')


@firstbp.route('/zuzhi', methods=['GET', 'POST'])
def zuzhi():
    if request.method == 'POST':
        data = get_data()
        if int(data['pid']) == 0:
            sql = "insert into organization(name,is_q,zzsuoxie,sort,pid) values" \
                  "('{}',{},'{}',{},0)".format(data['name'], int(data['status']),
                                               data['zzsuoxie'], int(data['sort']))
        else:
            sql = "insert into organization(name,is_q,zzsuoxie,sort,pid) values" \
                  "('{}',{},'{}',{},{})".format(data['name'], int(data['status']),
                                                data['zzsuoxie'], int(data['sort']), int(data['pid']))
        db.update(sql)
        return jsonify(({'code': 200, 'msg': '添加成功'}))
    else:
        name = request.args.get('name')
        if name:
            sql1 = "select * from organization where name='{}'".format(name)
            res1 = db.one(sql1)
            return jsonify({'code': 200, 'list': res1})
        else:
            sql = "select * from organization"
            res = db.all(sql)
            list = xtree(res)
            return jsonify({'code': 200, 'list': list})


# 导入excel
@firstbp.route('/datafile', methods=['POST'])
def datafile():
    # 获取文件
    file = request.files['file']
    newname = myfile.rename(file.filename)
    # 上传文件
    file.save(os.path.join('./static/files/' + newname))
    # 打开文件读取
    name = './static/files/' + newname
    bk = open_workbook(name, encoding_override='utf-8')
    sh = bk.sheets()[0]
    nrows = sh.nrows
    ncols = sh.ncols
    for i in range(2, nrows):
        row_date = sh.row_values(i)
        for j in range(0, ncols):
            print(row_date[j])
    return jsonify({'code': 200, 'msg': '成功'})


# 导出excel
@firstbp.route('/download', methods=["GET", "POST"])
def download():
    # 实例化二进制数据
    out = BytesIO()
    # 创建一个Excel实例
    workbook = xlsxwriter.Workbook(out)
    # 添加一个sheet，add_worksheet()，默认"Sheet1"，可自定义命名，add_worksheet("资产列表")
    table = workbook.add_worksheet()
    # table.write(行, 列, 字段名)
    # 行、列从 0 开始计数
    # 字段命名根据每个人使用情况而定，插入数据部分也是
    sql = "select * from organization"
    res = db.all(sql)
    table.write(0, 0, "编号")
    table.write(0, 1, "名称")
    num = 1
    for i in res:
        table.write(num, 0, i['id'])
        table.write(num, 1, i['name'])
        num += 1

    workbook.close()
    filename = quote("资产列表_{}.xlsx".format(datetime.datetime.now().strftime("%Y%m%d%H%M%S")))
    file = make_response(out.getvalue())
    out.close()
    mime_type = mimetypes.guess_type('%s.xlsx' % filename)[0]
    file.headers['Content-Type'] = mime_type
    file.headers["Cache-Control"] = "no-cache"
    file.headers['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(filename)
    return file


@firstbp.route('/tttest')
def tttst():
    es = Myes('ttest', 'ttest-type')
    # 插入数据
    sql = "select * from organization limit 0,3"
    res = db.all(sql)
    for i in res:
        es.insert(i)

    return 'ok'


# 获取数据
@firstbp.route('/get_tttest')
def get_tttest():
    es = Myes('ttest', 'ttest-type')
    query = {'match': {'name': '小蒯子'}}
    data = es.search(query)
    return 'ok'


@firstbp.route('/bumen', methods=['GET', 'POST'])
def bumen():
    if request.method == 'POST':
        data = get_data()
        sql = "insert into bumen(name,code,sort,content) values('{}','{}',{},'{}')".format(data['name'], data['code'],
                                                                                           int(data['sort']),
                                                                                           data['content'])
        db.update(sql)
        return jsonify({'code': 200, 'msg': '添加成功'})

    else:
        sql = "select * from bumen"
        res = db.all(sql)
        return jsonify({'code': 200, 'list': res})


@firstbp.route('/role')
def role():
    time.sleep(1)
    sql = "select * from role"
    res = db.all(sql)
    return jsonify({'code': 200, 'list': res})


@firstbp.route('/gangwei')
def gangwei():
    time.sleep(1)
    sql = "select * from gangwei"
    res = db.all(sql)
    return jsonify({'code': 200, 'list': res})


@firstbp.route('/uplod', methods=['POST'])
def uplod():
    file = request.files['file']
    file.save(os.path.join('./static/img/' + file.filename))
    img = 'http://localhost:5000/static/img/' + file.filename
    return jsonify({'code': 200, 'img': img})


@firstbp.route('/users', methods=['GET', 'POST'])
def users():
    if request.method == 'POST':
        data = get_data()
        sql = "insert into users(account,password,name,bum_id,role_id,sort,lever,qq,sex,mobile,is_user,job_number,gangwei_id,img,birthday,email,phone) values('%s','%s','%s',%d,%d,%d,%d,'%s','%s','%s',%d,'%s',%d,'%s','%s','%s','%s')" % (
            data['account'], data['password'], data['name'], int(data['bum_id']), int(data['role_id']),
            int(data['sort']),
            int(data['level']), data['qq'], data['sex'], data['mobile'], int(data['is_user']), data['job_number'],
            int(data['gangwei_id']), data['img'], data['birthday'], data['email'], data['phone'])
        db.update(sql)
        return jsonify({'code': 200, 'msg': '添加成功'})
    else:
        sql = "select * from users"
        res = db.all(sql)
        return jsonify({'code': 200, 'list': res})
